"""
系统表Schema定义

定义用于存储用户权限信息的系统表结构
"""

import sys
import os

# 添加OperatingSystem路径以导入table_schema
current_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.dirname(current_dir)
operating_system_dir = os.path.join(parent_dir, "OperatingSystem")
if operating_system_dir not in sys.path:
    sys.path.insert(0, operating_system_dir)

from table_schema import Column, ColumnType


# 系统数据库名称
SYSTEM_DATABASE_NAME = "system"

# 用户基本信息表
SYSTEM_USERS_SCHEMA = [
    Column("user_id", ColumnType.INT, primary_key=True, nullable=False),  # 使用单一主键
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False),
    Column("password_hash", ColumnType.VARCHAR, length=255, nullable=False),
    Column("created_time", ColumnType.DATE, nullable=False),
    Column("last_login", ColumnType.DATE, nullable=True),
    Column("is_active", ColumnType.BOOLEAN, nullable=False),
    Column("max_connections", ColumnType.INT, nullable=False)
]

# 全局权限表
SYSTEM_GLOBAL_PRIVS_SCHEMA = [
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False), 
    Column("privilege_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("is_grantable", ColumnType.BOOLEAN, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False),
    Column("granted_by", ColumnType.VARCHAR, length=64, nullable=True)
]

# 数据库权限表
SYSTEM_DB_PRIVS_SCHEMA = [
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False),
    Column("db_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("privilege_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("is_grantable", ColumnType.BOOLEAN, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False),
    Column("granted_by", ColumnType.VARCHAR, length=64, nullable=True)
]

# 表权限表
SYSTEM_TABLE_PRIVS_SCHEMA = [
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False),
    Column("db_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("table_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("privilege_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("is_grantable", ColumnType.BOOLEAN, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False),
    Column("granted_by", ColumnType.VARCHAR, length=64, nullable=True)
]

# 列权限表
SYSTEM_COLUMN_PRIVS_SCHEMA = [
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False),
    Column("db_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("table_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("column_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("privilege_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("is_grantable", ColumnType.BOOLEAN, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False),
    Column("granted_by", ColumnType.VARCHAR, length=64, nullable=True)
]

# 角色定义表
SYSTEM_ROLES_SCHEMA = [
    Column("role_name", ColumnType.VARCHAR, length=64, primary_key=True, nullable=False),
    Column("description", ColumnType.VARCHAR, length=255, nullable=True),
    Column("created_time", ColumnType.DATE, nullable=False),
    Column("is_active", ColumnType.BOOLEAN, nullable=False)
]

# 角色权限表
SYSTEM_ROLE_PRIVS_SCHEMA = [
    Column("role_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("resource_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("resource_name", ColumnType.VARCHAR, length=128, nullable=False),
    Column("privilege_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("is_grantable", ColumnType.BOOLEAN, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False)
]

# 用户角色关系表
SYSTEM_USER_ROLES_SCHEMA = [
    Column("username", ColumnType.VARCHAR, length=64, nullable=False),
    Column("host", ColumnType.VARCHAR, length=64, nullable=False),
    Column("role_name", ColumnType.VARCHAR, length=64, nullable=False),
    Column("granted_time", ColumnType.DATE, nullable=False),
    Column("granted_by", ColumnType.VARCHAR, length=64, nullable=True)
]

# 审计日志表
SYSTEM_AUDIT_LOG_SCHEMA = [
    Column("log_id", ColumnType.INT, primary_key=True, nullable=False),
    Column("username", ColumnType.VARCHAR, length=64, nullable=True),
    Column("host", ColumnType.VARCHAR, length=64, nullable=True),
    Column("operation_type", ColumnType.VARCHAR, length=32, nullable=False),
    Column("resource_type", ColumnType.VARCHAR, length=32, nullable=True),
    Column("resource_name", ColumnType.VARCHAR, length=128, nullable=True),
    Column("operation_time", ColumnType.DATE, nullable=False),
    Column("status", ColumnType.VARCHAR, length=16, nullable=False),
    Column("details", ColumnType.VARCHAR, length=500, nullable=True)
]

# 系统表定义映射
SYSTEM_TABLES = {
    "users": SYSTEM_USERS_SCHEMA,
    "global_privs": SYSTEM_GLOBAL_PRIVS_SCHEMA,
    "db_privs": SYSTEM_DB_PRIVS_SCHEMA,
    "table_privs": SYSTEM_TABLE_PRIVS_SCHEMA,
    "column_privs": SYSTEM_COLUMN_PRIVS_SCHEMA,
    "roles": SYSTEM_ROLES_SCHEMA,
    "role_privs": SYSTEM_ROLE_PRIVS_SCHEMA,
    "user_roles": SYSTEM_USER_ROLES_SCHEMA,
    "audit_log": SYSTEM_AUDIT_LOG_SCHEMA
}


def get_system_table_schema(table_name: str):
    """
    获取系统表的Schema定义
    
    Args:
        table_name: 系统表名称
        
    Returns:
        List[Column]: 表的列定义
    """
    return SYSTEM_TABLES.get(table_name)


def get_all_system_tables():
    """
    获取所有系统表名称
    
    Returns:
        List[str]: 系统表名称列表
    """
    return list(SYSTEM_TABLES.keys())
